import pandas as pd
import pymysql
import warnings

warnings.filterwarnings('ignore')

class CourseMapper:
    def __init__(self):
        self.host = "localhost"
        self.user = "root"
        self.password = "123456"
        self.database = "student_portrait_system"
        self.conn = pymysql.connect(host=self.host, user=self.user, password=self.password, database=self.database)

    # 读取所有课程信息
    def get_all_courses(self):
        sql = "SELECT * FROM course_info"
        df = pd.read_sql(sql, self.conn)
        return df

    # 根据课程名称修改课程信息
    def update_course_by_name(self, course_name, cat, teacher):
        sql = "UPDATE course_info SET cat=%s, teacher=%s WHERE course_name=%s"
        params = (cat, teacher, course_name)
        cursor = self.conn.cursor()
        # 执行SQL语句
        cursor.execute(sql, params)
        # commit
        self.conn.commit()
        # 返回是否更新成功

        return cursor.rowcount > 0

    # 根据课程名称删除课程信息
    def delete_course_by_name(self, course_name, ):
        sql = "DELETE FROM course_info WHERE course_name=%s"
        params = (course_name,)
        cursor = self.conn.cursor()
        # 执行SQL语句
        cursor.execute(sql, params)
        self.conn.commit()
        # 返回是否删除成功
        return cursor.rowcount > 0

    # 新增课程信息
    def add_course(self, course_name, cat, teacher):
        sql = "INSERT INTO course_info (course_name, cat, teacher) VALUES (%s, %s, %s)"
        params = (course_name, cat, teacher)
        cursor = self.conn.cursor()
        # 执行SQL语句
        cursor.execute(sql, params)
        self.conn.commit()

        # 返回是否新增成功
        return cursor.rowcount > 0

    # 根据课程名称查询课程信息
    def get_course_by_name(self, course_name):
        sql = "SELECT * FROM course_info WHERE course_name=%s"
        params = (course_name,)
        cursor = self.conn.cursor()
        # 执行SQL语句
        cursor.execute(sql, params)
        return tuple(cursor.fetchall())
